package com.exam.userms.dbmanage.helper;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.exam.userms.UserData;

import java.util.LinkedList;
import java.util.List;

/**
 * 用户数据管理类
 * Created by guopei on 2020/7/3.主要是用户信息的管理操作
 */
public class UserDataManager {

    //一些宏定义和声明
    private static final String TAG = "UserDataManager";
    private static final String DB_NAME = "user";
    private static final String TABLE_NAME = "users";
    public static final String ID = "id";
    public static final String USER_NAME = "name";
    public static final String USER_PHONE = "phone";
    private static final int DB_VERSION = 3;
    private Context mContext = null;

    //创建用户book表
    private static final String DB_CREATE = "CREATE TABLE " + TABLE_NAME + " ("
            + ID + " integer primary key," + USER_NAME + " varchar,"
            + USER_PHONE + " varchar" + ");";

    private SQLiteDatabase mSQLiteDatabase = null;
    private DataBaseManagementHelper mDatabaseHelper = null;

    //DataBaseManagementHelper继承自SQLiteOpenHelper
    private static class DataBaseManagementHelper extends SQLiteOpenHelper {

        DataBaseManagementHelper(Context context) {
            super(context, DB_NAME, null, DB_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            Log.i(TAG, "db.getVersion()=" + db.getVersion());
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME + ";");
            db.execSQL(DB_CREATE);
            Log.i(TAG, "db.execSQL(DB_CREATE)");
            Log.e(TAG, DB_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.i(TAG, "DataBaseManagementHelper onUpgrade");
            onCreate(db);
        }
    }

    public UserDataManager(Context context) {
        mContext = context;
        Log.i(TAG, "UserDataManager construction!");
    }

    //打开数据库
    public void openDataBase() throws SQLException {
        mDatabaseHelper = new DataBaseManagementHelper(mContext);
        mSQLiteDatabase = mDatabaseHelper.getWritableDatabase();
    }

    //关闭数据库
    public void closeDataBase() throws SQLException {
        mDatabaseHelper.close();
    }

    //添加新用户，即注册
    public long insertUserData(UserData userData) {
        String userName = userData.getName();
        String userPhone = userData.getPhone();
        ContentValues values = new ContentValues();
        values.put(USER_NAME, userName);
        values.put(USER_PHONE, userPhone);
        return mSQLiteDatabase.insert(TABLE_NAME, ID, values);
    }

    /**
     * 批量插入数据
     * @param initList
     */
    public void batchInsertUser(List<UserData> initList) {
        if (initList == null || initList.size() == 0) {
            return;
        }
        for (UserData userData : initList) {
            ContentValues values = new ContentValues();
            values.put(USER_NAME, userData.getName());
            values.put(USER_PHONE, userData.getPhone());
            mSQLiteDatabase.insert(TABLE_NAME, ID, values);
        }
    }

    //更新用户信息
    public boolean updateUserData(UserData userData) {
        //int id = userData.getUserId();
        String userName = userData.getName();
        String userPhone = userData.getPhone();
        ContentValues values = new ContentValues();
        values.put(USER_NAME, userName);
        values.put(USER_PHONE, userPhone);
        return mSQLiteDatabase.update(TABLE_NAME, values, null, null) > 0;
    }

    //
    public Cursor fetchUserData(int id) throws SQLException {
        Cursor mCursor = mSQLiteDatabase.query(false, TABLE_NAME, null, ID
                + "=" + id, null, null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }

    //
    public Cursor fetchAllUserDatas() {
        return mSQLiteDatabase.query(TABLE_NAME, null, null, null, null, null,
                null);
    }

    //根据id删除用户
    public boolean deleteUserData(int id) {
        return mSQLiteDatabase.delete(TABLE_NAME, ID + "=" + id, null) > 0;
    }

    //根据ids批量删除用户
    public boolean deleteUserData(List<Integer> ids) {
        if (ids == null || ids.size() == 0) {
            return false;
        }
        for (Integer id : ids) {
            mSQLiteDatabase.delete(TABLE_NAME, ID + "=" + id, null);
        }
        return true;
    }

    //根据用户名注销
    public boolean deleteUserDatabyname(String name) {
        return mSQLiteDatabase.delete(TABLE_NAME, USER_NAME + "=" + name, null) > 0;
    }

    //删除所有用户
    public boolean deleteAllUserDatas() {
        return mSQLiteDatabase.delete(TABLE_NAME, null, null) > 0;
    }

    //
    public String getStringByColumnName(String columnName, int id) {
        Cursor mCursor = fetchUserData(id);
        int columnIndex = mCursor.getColumnIndex(columnName);
        String columnValue = mCursor.getString(columnIndex);
        mCursor.close();
        return columnValue;
    }

    //
    public boolean updateUserDataById(String columnName, int id,
                                      String columnValue) {
        ContentValues values = new ContentValues();
        values.put(columnName, columnValue);
        return mSQLiteDatabase.update(TABLE_NAME, values, ID + "=" + id, null) > 0;
    }

    //根据用户名找用户
    public int findUserByName(String userName) {
        Log.i(TAG, "findUserByName , userName=" + userName);
        int result = 0;
        Cursor mCursor = mSQLiteDatabase.query(TABLE_NAME, null, USER_NAME + "=" + userName, null, null, null, null);
        if (mCursor != null) {
            result = mCursor.getCount();
            mCursor.close();
            Log.i(TAG, "findUserByName , result=" + result);
        }
        return result;
    }

    //根据用户名和手机号找用户
    public int findUserByNameAndPwd(String userName, String pwd) {
        Log.i(TAG, "findUserByNameAndPhone");
        int result = 0;
        Cursor mCursor = mSQLiteDatabase.query(TABLE_NAME, null, USER_NAME + "=" + userName + " and " + USER_PHONE + "=" + pwd,
                null, null, null, null);
        if (mCursor != null) {
            result = mCursor.getCount();
            mCursor.close();
            Log.i(TAG, "findUserByNameAndPhone , result=" + result);
        }
        return result;
    }

    /**
     * 查询用户
     *
     * @return
     */
    public List<UserData> findAll() {
        List<UserData> list = new LinkedList<>();

        String sql = "select id, name, phone from " + TABLE_NAME;
        Cursor cursor = mSQLiteDatabase.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            UserData user = new UserData();
            user.setId(cursor.getInt(cursor.getColumnIndex("id")));
            user.setName(cursor.getString(cursor.getColumnIndex("name")));
            user.setPhone(cursor.getString(cursor.getColumnIndex("phone")));
            list.add(user);
        }
        return list;
    }

}
